# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors
from xlwt import *
import xlrd
import sys
from xlutils.copy import copy
import os
import cvs2PersionListManagerCompare

reload(sys)
sys.setdefaultencoding('utf8')

"""
author：zhangqing
function: 数据初始化对比：cvs1.0与cvs2.0 人物基本信息对比（中文名、英文名、国家、城市、省、人物类型、中文描述、英文描述）
"""
#flagType=2用此变量存放1.0和2.0数据库符合条件(1.0迁到2.0库persion基本信息正确)的persionId
persionIdList = []
def searchCvs1Persion():
    db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)

    cursor = db.cursor()

    sql = "SELECT COUNT(a.id) a FROM datacenter_online_20180609.dc_personage a ORDER BY a.id DESC "
    cursor.execute(sql)
    resultsCount = cursor.fetchone()
    pageCount = resultsCount["a"]/500

    for index in range(pageCount):
        limitStart = index * 500
        print("limitStart====================================================",limitStart)

        sql = "SELECT a.cn_name, a.en_name, a.sex, a.photograph, if(a.education = 1, 5, if(a.education = 2, 4, if(a.education = 3, 3, if(a.education = 4, 2, null)))) AS education," \
              " a.remark, a.country, a.province, a.city, a.cn_description, a.en_description, a.quality_status, a.info_status," \
              "a.creater_id AS create_id, a.create_time, a.modifier_id AS modify_id, a.modify_time,a.`status`, a.id, b.personage_type_value persionType " \
              "FROM datacenter_online_20180609.dc_personage a " \
              "LEFT JOIN datacenter_online_20180609.dc_personage_type b " \
              "ON a.id=b.personage_id ORDER BY a.id DESC limit %d,500 "%limitStart

        # sql = "SELECT a.cn_name, a.en_name, a.sex, a.photograph, if(a.education = 1, 5, if(a.education = 2, 4, if(a.education = 3, 3, if(a.education = 4, 2, null)))) education," \
        #       " a.remark, a.country, a.province, a.city, a.cn_description, a.en_description, a.quality_status, a.info_status," \
        #       "a.creater_id AS create_id, a.create_time, a.modifier_id AS modify_id, a.modify_time,a.`status`, a.id, b.personage_type_value persionType " \
        #       "FROM datacenter_online_20180609.dc_personage a " \
        #       "LEFT JOIN datacenter_online_20180609.dc_personage_type b " \
        #       "ON a.id=b.personage_id WHERE a.cn_name in ('Tomi Xue')"

        try:
           cursor.execute(sql)
           results1 = cursor.fetchall()
           searchCvs2Persion(results1)
           # if flagType==2:
           #     cvs2PersionManagerCompare.searchCvs1PersionManager(persionIdList)

        except:
           print "Error: unable to fecth data"

    db.close()

def searchCvs2Persion(results1):
    db = MySQLdb.connect("rm-2ze9cx91355z4mk9hpo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6",
                         "cms_plus", charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    for index in range(len(results1)):

        cn_name1 = results1[index].get(u"cn_name")
        en_name1 = results1[index].get(u"en_name")
        print("en_name1=",en_name1)
        sex1 = results1[index].get(u"sex")
        education1 = results1[index].get(u"education")
        country1 = results1[index].get(u"country")
        province1 = results1[index].get(u"province")
        city1 = results1[index].get(u"city")
        persionType1 = results1[index].get(u"persionType")
        cn_description1 = results1[index].get(u"cn_description")
        en_description1 = results1[index].get(u"en_description")

        if cn_name1 ==None and en_name1==None:
            results1[index].update(beizhu=u'cvs1中此数据cn_name和en_name同时为空，不符合两个必填一个要求，注意cvs2中此类型数据')
            writeExcel(results1[index],fields)
            continue

        sqlParam  = tuple([])
        sql = "SELECT a.cn_name, a.en_name, a.sex, a.photograph, a.education," \
              " a.remark, a.country, a.province, a.city, a.cn_description, a.en_description, a.quality_status, a.info_status, " \
              "a.create_id , a.create_time, a.modify_id, a.modify_time, a.status, a.id, b.person_type persionType ,a.enable_search as beizhu " \
              "FROM cms_plus.tbl_person a " \
              "LEFT JOIN cms_plus.tbl_person_type b ON a.id=b.person_id WHERE a.`status`=1"

        if cn_name1!=None:
            sql += " AND a.cn_name = '%s'"
            sqlParam += tuple([cn_name1])

        if en_name1!=None:
            sql += " AND a.en_name = '%s'"
            sqlParam += tuple([en_name1])
        else:
            sql += " AND a.en_name is null"

        if country1!=None:
            sql += " AND a.country = '%s'"
            sqlParam += tuple([country1])

        if province1!=None:
            sql += " AND a.province = '%s'"
            sqlParam += tuple([province1])

        if city1!=None:
            sql += " AND a.city = '%s'"
            sqlParam += tuple([city1])

        if sex1!=None:
            sql += " AND a.sex = '%s'"
            sqlParam += tuple([sex1])

        if cn_description1!=None:
             cn_description1 = MySQLdb.escape_string(cn_description1)
             sql += " AND a.cn_description = '%s'"
             sqlParam += tuple([cn_description1])
        else:
            sql += " AND a.cn_description is null"

        if en_description1!=None:
            en_description1 = MySQLdb.escape_string(en_description1)
            sql += " AND a.en_description = '%s'"
            sqlParam += tuple([en_description1])
        else:
            sql += " AND a.en_description is null"

        # if education1!=None:
        #     sql += " AND a.education = '%s'"
        #     sqlParam += tuple([education1])

        if persionType1!=None:
            sql += " AND b.person_type = '%s'"
            sqlParam += tuple([persionType1])

        try:

           cursor.execute(sql %(sqlParam))
           print("cvs2.0sql===",sql %(sqlParam))
           results2 = cursor.fetchall()
           fields = cursor.description
           if len(results2)==0:
               results1[index].update(beizhu=u'cvs2中无完全符合条件数据,此为cvs1中数据')
               writeExcel(results1[index],fields)

           for index2 in range(len(results2)):

                cn_name2 = results2[index2].get(u"cn_name")
                en_name2 = results2[index2].get(u"en_name")
                education2 = results2[index2].get(u"education")
                country2 = results2[index2].get(u"country")
                province2 = results2[index2].get(u"province")
                city2 = results2[index2].get(u"city")
                persionType2 = results2[index2].get(u"persionType")
                cn_description2 = results2[index2].get(u"cn_description")
                if cn_description2!=None:
                    cn_description2 = MySQLdb.escape_string(cn_description2)
                en_description2 = results2[index2].get(u"en_description")
                if en_description1!=None:
                    en_description2 = MySQLdb.escape_string(en_description2)

                flag = (cn_name1 == cn_name2 and en_name1 == en_name2 and country1 == country2 and province1 == province2 and city1 == city2 and persionType1 == persionType2 and cn_description1 == cn_description2 and en_description1 == en_description2)
                #flagType==1 代表只筛选1.0库中与2.0库中不符合的数据
                # if flagType ==1:
                if flag != True:
                    results1[index].update(beizhu=u'cvs1和2中cn_name,en_name,education,country,province,city,persionType,cn_description,en_description不完全相同，cvs1数据')
                    writeExcel(results1[index],fields)
                    results2[index2].update(beizhu=u'cvs1和2中cn_name,en_name,education,country,province,city,persionType,cn_description,en_description不完全相同，cvs2数据')
                    writeExcel(results2[index2],fields)
                        # print results2
                #flagType==2 代表获取1.0库中与2.0库中符合条件成功匹配的数据的persionId，为职业生涯的判断做准备
                # elif flagType == 2:
                #     if flag == True:
                #         persionIdDict = {}
                #         persionIdDict["persionId1"] = results1[index].get(u"id")
                #         persionIdDict["persionId2"] = results2[index2].get(u"id")
                #         persionIdList.append(persionIdDict)

        except:
           print "Error: unable to fecth data"

    db.close()

def writeExcel(results,fields):

    p1 = os.path.exists('data.xlsx')
    if p1:
        f = xlrd.open_workbook('data.xlsx', formatting_info=True)
        sheet = f.sheet_by_index(0)
        rowNum = sheet.nrows
        newbook =copy(f)
        newsheet = newbook.get_sheet(0)

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print rowNum,j,x
            newsheet.write(rowNum,j,x)

        fileName = 'data.xlsx'
        newbook.save(fileName)

    else:

        workbook  = Workbook(encoding = 'utf-8')
        table = workbook .add_sheet('data')

        for field in range(0,len(fields)):
            table.write(0,field,fields[field][0])

        # for i,p in enumerate(results):
        #     num =0
        #     for j,q in enumerate(p):
        #         x = p["%s"%fields[num][0]]
        #         num = num+1
        #         print i,j,x
        #         table.write(i+1,j,x)

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print 1,j,x
            table.write(1,j,x)

        fileName = 'data.xlsx'
        workbook .save(fileName)
